package com.ihr360.oneclick.service.impl;

import cn.irenshi.core.utils.QrCodeUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.TypeReference;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.ihr360.commons.lang.DateUtils;
import com.ihr360.message.entity.MessageSendRecord;
import com.ihr360.message.entity.MessageSendTask;
import com.ihr360.message.entity.MessageTemplate;
import com.ihr360.message.service.MessageSendTaskService;
import com.ihr360.message.service.MessageTemplateService;
import com.ihr360.oneclick.config.SmsConfig;
import com.ihr360.oneclick.constant.ConstantString;
import com.ihr360.oneclick.constant.DtoFieldName;
import com.ihr360.oneclick.dao.CompanyFreeMessageDao;
import com.ihr360.oneclick.dao.OneClickTermMetaDao;
import com.ihr360.oneclick.dao.SalaryTypeDao;
import com.ihr360.oneclick.dao.SpreadsheetColumnDao;
import com.ihr360.oneclick.dao.SpreadsheetDao;
import com.ihr360.oneclick.dao.SpreadsheetFootColumnDao;
import com.ihr360.oneclick.dao.SpreadsheetRowDao;
import com.ihr360.oneclick.dao.SpreadsheetRowHeaderDao;
import com.ihr360.oneclick.dao.SpreadsheetTempRowDao;
import com.ihr360.oneclick.dao.StaffRecordDao;
import com.ihr360.oneclick.dto.CompanyFreeMessageDto;
import com.ihr360.oneclick.dto.OneClickTermMetaDto;
import com.ihr360.oneclick.dto.SalaryTypeSettingDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadSheetRowHeaderDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetColumnDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetFootColumnDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetRowDto;
import com.ihr360.oneclick.entity.NotifyStateType;
import com.ihr360.oneclick.entity.OneClickTerm;
import com.ihr360.oneclick.entity.RowHeaderColumn;
import com.ihr360.oneclick.entity.Spreadsheet;
import com.ihr360.oneclick.entity.SpreadsheetRow;
import com.ihr360.oneclick.exception.OneClickException;
import com.ihr360.oneclick.modal.OneClickStaff;
import com.ihr360.oneclick.request.MessageSendRequest;
import com.ihr360.oneclick.request.SearchRequest;
import com.ihr360.oneclick.request.SpreadsheetDetailRequest;
import com.ihr360.oneclick.request.SpreadsheetFootColumnRequest;
import com.ihr360.oneclick.request.SpreadsheetPublishRequest;
import com.ihr360.oneclick.request.query.SpreadsheetQueryParam;
import com.ihr360.oneclick.service.RowHeaderColumnService;
import com.ihr360.oneclick.service.SalaryTypeService;
import com.ihr360.oneclick.service.SpreadsheetRowService;
import com.ihr360.oneclick.service.SpreadsheetService;
import com.ihr360.oneclick.service.SpreadsheetTempRowService;
import com.ihr360.oneclick.util.BeanConvertUtils;
import com.ihr360.oneclick.util.MD5Utils;
import com.ihr360.oneclick.util.PageableUtils;
import com.ihr360.oneclick.vo.FootColumnVo;
import com.ihr360.oneclick.vo.PageData;
import com.ihr360.oneclick.vo.SpreadsheetDetailVo;
import com.ihr360.oneclick.vo.SpreadsheetVo;
import com.ihr360.rest.service.IrenshiDubboProviderService;
import com.ihr360.rest.service.IrenshiWeixinService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Base64;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 电子表格业务层
 *
 * @author tom
 * @date 17-12-18
 */
@Service
public class SpreadsheetServiceImpl implements SpreadsheetService {

    private final static Logger logger = LoggerFactory.getLogger(SpreadsheetServiceImpl.class);
    private static final String NOT_AVALIABLE = "N/A";

    @Autowired
    private SpreadsheetDao spreadsheetDao;
    @Autowired
    private OneClickTermMetaDao oneClickTermMetaDao;
    @Autowired
    private SpreadsheetRowDao spreadsheetRowDao;
    @Autowired
    private SpreadsheetRowHeaderDao spreadsheetRowHeaderDao;
    @Autowired
    private SpreadsheetColumnDao spreadsheetColumnDao;
    @Autowired
    private SpreadsheetRowService spreadsheetRowService;
    @Autowired
    private SpreadsheetTempRowService spreadsheetTempRowService;
    @Autowired
    private RowHeaderColumnService rowHeaderColumnService;
    @Autowired
    private MessageSendTaskService messageSendTaskService;
    @Autowired
    private MessageTemplateService messageTemplateService;
    @Autowired
    private StaffRecordDao staffRecordDao;
    @Autowired
    private SpreadsheetFootColumnDao spreadsheetFootColumnDao;
    @Autowired
    private SpreadsheetTempRowDao spreadsheetTempRowDao;
    @Autowired
    private IrenshiDubboProviderService irenshiDubboProviderService;
    @Autowired
    private CompanyFreeMessageDao companyFreeMessageDao;
    @Autowired
    private SmsConfig smsConfig;
    @Autowired
    private SalaryTypeService salaryTypeService;
    @Autowired
    private SalaryTypeDao salaryTypeDao;
    @Autowired
    private SpreadsheetRowDao spreadSheetRowDao;
    @Autowired
    private IrenshiWeixinService irenshiWeixinService;
    @Value("${one.click.url}")
    private String url;


    private Specification<SpreadsheetDto> buildSpecification(String companyId, String userId, SpreadsheetQueryParam queryParam) {
        return (root, cq, cb) -> {
            Predicate predicate = cb.conjunction();
            List<Expression<Boolean>> expressionList = predicate.getExpressions();
            expressionList.add(cb.equal(root.<String>get(DtoFieldName.Spreadsheet.COMPANY_ID), companyId));
            expressionList.add(cb.equal(root.<OneClickTerm>get(DtoFieldName.Spreadsheet.TERM).<String>get("id"), queryParam.getTermId()));
            //expressionList.add(cb.equal(root.<UserSpreadsheetDto>get("owner").<String>get("userId"), userId));
            expressionList.add(cb.equal(root.<Integer>get(DtoFieldName.Spreadsheet.STATUS), 1));
            if (queryParam.getGtOrEqStartMonth() != null) {
                expressionList.add(cb.greaterThanOrEqualTo(root.get(DtoFieldName.Spreadsheet.MONTH), queryParam.getGtOrEqStartMonth()));
            }
            if (queryParam.getLtOrEqEndMonth() != null) {
                expressionList.add(cb.lessThanOrEqualTo(root.get(DtoFieldName.Spreadsheet.MONTH), queryParam.getLtOrEqEndMonth()));
            }
            return predicate;
        };
    }

    @Override
    public PageData<Spreadsheet> getPageDataByCompanyId(String companyId, String userId, SearchRequest searchRequest) {
        if (searchRequest.getSortField() == null) {
            searchRequest.setSortField("createdAt");
            searchRequest.setSortDirection(Sort.Direction.DESC);
        }
        Page<SpreadsheetDto> page = spreadsheetDao.findAll(buildSpecification(companyId, userId,
                (SpreadsheetQueryParam) searchRequest.getQueryParam()),
                PageableUtils.generatePageable(searchRequest));
        PageData<Spreadsheet> pageData = new PageData<>(page);
        List<Spreadsheet> spreadsheetList = new ArrayList<>();
        List<SalaryTypeSettingDto> salaryType = salaryTypeService.findSalaryType(companyId);
        Map<String, String> salaryMap = salaryType.stream().collect(Collectors.toMap(SalaryTypeSettingDto::getId, salaryTypeSettingDto -> salaryTypeSettingDto.getTypeName()));
        page.getContent().forEach(spreadsheetDto -> {
            long read = spreadsheetRowDao.countBySpreadsheetAndIsRead(spreadsheetDto, true);
            Spreadsheet spreadsheet = BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet());
            spreadsheet.setRead(read);
            spreadsheet.setUnread(spreadsheet.getRowCount() - read);
            spreadsheet.setSmsCount(messageSendTaskService.getSMSCountBySheetId(spreadsheetDto.getId()));
            spreadsheet.setErrorCount(messageSendTaskService.getErrorCountBySheetId(spreadsheetDto.getId()));
            spreadsheet.setSalaryName(salaryMap.get(spreadsheetDto.getSalaryTypeId()));
            spreadsheetList.add(spreadsheet);
        });
        pageData.setContent(spreadsheetList);
        return pageData;
    }

    @Override
    public Spreadsheet getSpreadsheetById(String spreadsheetId) {
        return null;
    }

    @Override
    public SpreadsheetDetailVo getSpreadsheetDetail(SpreadsheetDetailRequest spreadsheetDetailRequest) {
        SpreadsheetDetailVo spreadsheetDetail = new SpreadsheetDetailVo();
        OneClickTermMetaDto oneClickTermMetaDto = oneClickTermMetaDao.findByTermIdAndMetaKey(spreadsheetDetailRequest.getTermId(),
                ConstantString.SPREADSHEET_EDIT_TIP);
        if (oneClickTermMetaDto != null) {
            spreadsheetDetail.setSheetTip(oneClickTermMetaDto.getMetaValue());
        }
        if (!spreadsheetDetailRequest.isNew()) {
            String spreadsheetId = spreadsheetDetailRequest.getSpreadsheetId();
            SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetDetailRequest.getSpreadsheetId());
            if (spreadsheetDto == null) {
                throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
            }
            BeanConvertUtils.convertFor(spreadsheetDto, spreadsheetDetail);

            //获取表头
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetId);
            List<RowHeaderColumn> columnList = spreadSheetRowHeaderDto.getData();
            int columnIndex = columnList.size();
            RowHeaderColumn weChatStatusColumn = new RowHeaderColumn();
            weChatStatusColumn.setFixedColumn(true);
            weChatStatusColumn.setColumnFieldName("微信发送");
            weChatStatusColumn.setColumnDisplayName("微信发送");
            weChatStatusColumn.setColumnIndex(columnIndex++);
            columnList.add(weChatStatusColumn);

            RowHeaderColumn emailStatusColumn = new RowHeaderColumn();
            emailStatusColumn.setFixedColumn(true);
            emailStatusColumn.setColumnFieldName("邮箱发送");
            emailStatusColumn.setColumnDisplayName("邮箱发送");
            emailStatusColumn.setColumnIndex(columnIndex++);
            columnList.add(emailStatusColumn);

            RowHeaderColumn smsStatusColumn = new RowHeaderColumn();
            smsStatusColumn.setFixedColumn(true);
            smsStatusColumn.setColumnFieldName("短信发送");
            smsStatusColumn.setColumnDisplayName("短信发送");
            smsStatusColumn.setColumnIndex(columnIndex++);
            columnList.add(smsStatusColumn);

            RowHeaderColumn readStatusColumn = new RowHeaderColumn();
            readStatusColumn.setFixedColumn(true);
            readStatusColumn.setColumnFieldName("阅读状态");
            readStatusColumn.setColumnDisplayName("阅读状态");
            readStatusColumn.setColumnIndex(columnIndex++);
            columnList.add(readStatusColumn);
            spreadsheetDetail.setRowHeader(columnList);
            spreadsheetDetail.setMonth(DateUtils.formatDateStr(spreadsheetDto.getMonth(), DateUtils.STANDARD_DATE_FORMAT_YM1));

            //获取行数据
            if (spreadsheetDetailRequest.isQueryTempRow()) {
                spreadsheetDetail.setPageData(spreadsheetTempRowService.getSpreadsheetRowPageData(spreadsheetDetailRequest.getCompanyId(),
                        spreadsheetId, spreadsheetDetailRequest.getSearchRequest()));
                //计算临时合计信息
                Map<String, String> tempFootMap = getTempFootData(spreadsheetId);
                spreadsheetDetail.setFootData(tempFootMap);
            } else {
                spreadsheetDetail.setPageData(spreadsheetRowService.getSpreadsheetRowPageData(spreadsheetDetailRequest.getCompanyId(),
                        spreadsheetId, spreadsheetDetailRequest.getSearchRequest()));
                //获取合计信息
                if (spreadsheetDto.getFooter() != null) {
                    Map<String, String> footMap = JSON.parseObject(spreadsheetDto.getFootDataStr(), new TypeReference<Map<String, String>>() {
                    });
                    spreadsheetDetail.setFootData(footMap);
                }
            }
        }
        return spreadsheetDetail;
    }

    @Override
    public SpreadsheetDetailVo getSpreadsheetTempDetail(SpreadsheetDetailRequest spreadsheetDetailRequest) {
        List<List<Object>> excelDatas = Lists.newArrayList();
        List<Object> excelHeadRow = Lists.newArrayList();
        List<Object> excelRow = null;
        SpreadsheetDetailVo spreadsheetDetail = new SpreadsheetDetailVo();
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetDetailRequest.getSpreadsheetId());
        if (spreadsheetDto == null) {
            throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
        }
        SalaryTypeSettingDto salaryTypeDaoOne = salaryTypeDao.findOne(spreadsheetDto.getSalaryTypeId());
        if (salaryTypeDaoOne == null) {
            throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
        }
        spreadsheetDetail.setTypeName(salaryTypeDaoOne.getTypeName());
        BeanConvertUtils.convertFor(spreadsheetDto, spreadsheetDetail);
        //获取表头
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetDetailRequest.getSpreadsheetId());
        List<RowHeaderColumn> rowHeaderColumns = spreadSheetRowHeaderDto.getData();
        rowHeaderColumns.forEach(rowHeaderColumn -> {
            excelHeadRow.add(rowHeaderColumn.getColumnDisplayName());
        });
        //判断表头是否存在邮箱这个字段
        /*if (excelHeadRow.size() >= 3) {
            excelHeadRow.set(0, "姓名");
            excelHeadRow.set(1, "手机号");
            List<Map<String, RowHeaderColumn>> excelFieldMapping = rowHeaderColumnService.getExcelFieldMapping(spreadsheetDetailRequest.getTermId());
            Map<String, RowHeaderColumn> rowHeaderColumnMap = excelFieldMapping.get(2);
            for (String rowHeaderColumn : rowHeaderColumnMap.keySet()) {
                if (rowHeaderColumn.equalsIgnoreCase(excelHeadRow.get(2) + "")) {
                    excelHeadRow.set(2, "邮箱");
                    break;
                }
            }
        } else {
            excelHeadRow.set(0, "姓名");
            excelHeadRow.set(1, "手机号");
        }*/
        excelDatas.add(excelHeadRow);
        spreadsheetDetail.setMonth(DateUtils.formatDateStr(spreadsheetDto.getMonth(), DateUtils.STANDARD_DATE_FORMAT_YM1));
        List<SpreadsheetRowDto> excelSpreadsheetRowList = spreadSheetRowDao.findBySpreadsheetIdOrderByCreatedAtRowIndexAsc(spreadsheetDetailRequest.getSpreadsheetId());
        for (SpreadsheetRowDto spreadsheetRow : excelSpreadsheetRowList) {
            List<Object> spreadsheetRowData = spreadsheetRow.getData();
            excelRow = Lists.newArrayList();
            for (Object rows : spreadsheetRowData) {
                excelRow.add(rows);
            }
            excelDatas.add(excelRow);
        }
        spreadsheetDetail.setExcelDatas(excelDatas);
        return spreadsheetDetail;
    }

    /**
     * 计算临时合计数据
     */
    private Map<String, String> getTempFootData(String spreadsheetId) {
        Map<String, String> stringDoubleMap = new HashMap<>();
        SpreadsheetFootColumnDto footColumnDto = spreadsheetFootColumnDao.findBySpreadsheetId(spreadsheetId);
        if (footColumnDto == null || footColumnDto.getData() == null) {
            return stringDoubleMap;
        }
        List<RowHeaderColumn> footColumnList = JSONArray.parseArray(footColumnDto.getDataStr(), RowHeaderColumn.class);
        if (CollectionUtils.isEmpty(footColumnList)) {
            return stringDoubleMap;
        }
        //获取临时表信息
        List<SpreadsheetRow> rowDtoList = spreadsheetTempRowService.getSpreadsheetRowBySpreadsheetId(spreadsheetId);
        if (CollectionUtils.isEmpty(rowDtoList)) {
            return stringDoubleMap;
        }

        List<List<Object>> rowValueList = new ArrayList<>();
        /*rowDtoList.forEach(rowDto ->
                rowValueList.add(JSONArray.parseArray(rowDto.getDataStr(), Object.class))
        );*/

        footColumnList.forEach(column ->
                stringDoubleMap.put(column.getColumnDisplayName(), itemValueTotal(rowValueList, column.getColumnIndex()))
        );

        return stringDoubleMap;
    }

    @Override
    public List<SpreadsheetRowDto> prePublishSpreadsheet(String companyId, String spreadsheetId) {
        Map<String, RowHeaderColumn> columnMap = rowHeaderColumnService.getSpreadsheetColumnMap(spreadsheetId);
        List<SpreadsheetRowDto> rowList = spreadsheetRowService.getCanPublishRow(companyId, spreadsheetId, columnMap);
        if (CollectionUtils.isEmpty(rowList)) {
            throw new OneClickException("one_click_term_no_data_can_released", "没有可发布的数据行！");
        }
        return rowList;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void publishSpreadsheet(String companyId, String userId, SpreadsheetPublishRequest spreadsheetPublishRequest) {
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetPublishRequest.getSpreadsheetId());
        List<SpreadsheetRowDto> rowList = prePublishSpreadsheet(companyId, spreadsheetPublishRequest.getSpreadsheetId());

        rowList.forEach(spreadsheetRowDto ->
                spreadsheetRowDto.setPublishStatus(true)
        );
        spreadsheetRowDao.save(rowList);
        spreadsheetDto.setPublishedCount(spreadsheetDto.getPublishedCount() + rowList.size());
        spreadsheetDto.setUnpublishedCount(spreadsheetDto.getUnpublishedCount() - rowList.size());
        spreadsheetDto.setLastPublishDate(new Date());
        spreadsheetDao.save(spreadsheetDto);

        List<MessageSendRequest> messageSendRequestList = spreadsheetPublishRequest.getMessageSendRequestList();
        if (CollectionUtils.isEmpty(messageSendRequestList)) {
            return;
        }

        // 所有发布的row
        List<SpreadsheetRowDto> publishRowAllList = spreadsheetRowDao.findBySpreadsheetIdAndPublishStatus(spreadsheetPublishRequest.getSpreadsheetId(), true);
        String month = DateUtils.formatDateStr(spreadsheetDto.getMonth(), DateUtils.STANDARD_DATE_FORMAT_YM1);
        String messageDesc = spreadsheetDto.getSheetDesc();
        String companyName = irenshiDubboProviderService.getCompanyName(companyId);


        List<MessageSendTask> messageSendTaskList = new ArrayList<>();
        for (MessageSendRequest messageSendRequest : messageSendRequestList) {
            MessageSendTask messageSendTask = buildMessageSendTask(companyId, userId, messageSendRequest,
                    messageSendRequest.isContainPublish() ? publishRowAllList : rowList, companyName, messageDesc, month);
            if (messageSendTask != null) {
                messageSendTaskList.add(messageSendTask);
            }
        }
        // 添加发送任务
        messageSendTaskList.forEach(messageSendTask ->
                messageSendTaskService.addSendTask(messageSendTask)
        );
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void publishSpreadSheetRow(String companyId, SpreadsheetPublishRequest spreadsheetPublishRequest) {
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetPublishRequest.getSpreadsheetId());
        List<SpreadsheetRowDto> spreadsheetRowDtoList = null;
        if (!CollectionUtils.isEmpty(spreadsheetPublishRequest.getSpreadSheetRowIds())) {
            spreadsheetRowDtoList = spreadsheetRowDao.findByIdIn(spreadsheetPublishRequest.getSpreadSheetRowIds());
        } else {
            spreadsheetRowDtoList = spreadsheetRowDao.findBySpreadsheetId(spreadsheetPublishRequest.getSpreadsheetId());
        }
        spreadsheetRowDtoList.forEach(spreadsheetRowDto -> {
            if (spreadsheetDto.getWeChatNotice() && !spreadsheetPublishRequest.getNotice()) {
                spreadsheetRowDto.setWeChatNoticeStatus(NotifyStateType.SEND_SUCCESS);
            }
            if (spreadsheetDto.getSmsNotice() && !spreadsheetPublishRequest.getNotice()) {
                spreadsheetRowDto.setSmsNoticeStatus(NotifyStateType.SEND_SUCCESS);
            }
            if (spreadsheetDto.getEmailNotice() && !spreadsheetPublishRequest.getNotice()) {
                spreadsheetRowDto.setMailNoticeStatus(NotifyStateType.SEND_SUCCESS);
            }
            spreadsheetRowDto.setRead(false);
            spreadsheetRowDto.setPublishStatus(true);
            spreadsheetRowDto.setUpdatedAt(new Date());
        });
        spreadsheetRowDao.save(spreadsheetRowDtoList);


        spreadsheetDto.setLastPublishDate(new Date());
        spreadsheetDao.save(spreadsheetDto);

        String month = DateUtils.formatDateStr(spreadsheetDto.getMonth(), DateUtils.STANDARD_DATE_FORMAT_YM1);
        String companyName = irenshiDubboProviderService.getCompanyName(companyId);
        String messageDesc = spreadsheetDto.getSheetDesc();
        List<MessageSendTask> messageSendTaskList = new ArrayList<>();
        if (spreadsheetDto.getEmailNotice()) {
            MessageSendRequest messageSendRequest = new MessageSendRequest();
            messageSendRequest.setMessageType(MessageSendRequest.MessageType.EMAIL);
            messageSendRequest.setLocal("zh_CN");
            messageSendRequest.setContainPublish(true);
            MessageSendTask messageSendTask = buildMessageSendTask(companyId, "", messageSendRequest
                    , spreadsheetRowDtoList, companyName, messageDesc, month);
            messageSendTask.setSheetId(spreadsheetDto.getId());
            messageSendTaskList.add(messageSendTask);
        }
        if (spreadsheetDto.getSmsNotice()) {
            MessageSendRequest messageSendRequest = new MessageSendRequest();
            messageSendRequest.setMessageType(MessageSendRequest.MessageType.SMS);
            messageSendRequest.setLocal("zh_CN");
            messageSendRequest.setContainPublish(true);
            MessageSendTask messageSendTask = buildMessageSendTask(companyId, "", messageSendRequest
                    , spreadsheetRowDtoList, companyName, messageDesc, month);
            messageSendTask.setSheetId(spreadsheetDto.getId());
            messageSendTaskList.add(messageSendTask);
        }
        if (spreadsheetDto.getWeChatNotice()) {
            MessageSendRequest messageSendRequest = new MessageSendRequest();
            messageSendRequest.setMessageType(MessageSendRequest.MessageType.WECHAT);
            messageSendRequest.setLocal("zh_CN");
            messageSendRequest.setContainPublish(true);
            MessageSendTask messageSendTask = buildMessageSendTask(companyId, "", messageSendRequest
                    , spreadsheetRowDtoList, companyName, messageDesc, month);
            messageSendTask.setSheetId(spreadsheetDto.getId());
            messageSendTaskList.add(messageSendTask);
        }

        if (spreadsheetPublishRequest.getNotice()) {
            messageSendTaskList.forEach(messageSendTask -> {
                messageSendTaskService.addSendTask(messageSendTask);
            });
        }
    }

    /**
     * 短信任务对象构建
     *
     * @param messageSendRequest
     * @param rowList
     * @param companyName
     * @param messageDesc
     * @param month
     */
    private MessageSendTask buildMessageSendTask(String companyId, String userId, MessageSendRequest messageSendRequest, List<SpreadsheetRowDto> rowList,
                                                 String companyName, String messageDesc, String month) {
        MessageSendTask messageSendTask = new MessageSendTask();
        List<MessageSendRecord> messageList = new ArrayList<>();
        messageSendTask.setCompanyId(companyId);
        messageSendTask.setUserId(userId);
        messageSendTask.setDoStatus(0);
        messageSendTask.setService(1);
        messageSendTask.setMessageType(messageSendRequest.getMessageType());
        messageSendTask.setDataDto(SpreadsheetRowDto.class.getName());

        // 获取员工名录
        //List<String> staffRecordIdList = rowList.stream().map(SpreadsheetRowDto::getStaffRecordId).collect(Collectors.toList());
        //List<StaffRecordDto> staffRecordDtoList = staffRecordDao.findByIdIn(staffRecordIdList);
        //Map<String, String> staffInfo = rowList.stream().collect(Collectors.toMap(SpreadsheetRowDto::getMobileNo, spreadsheetRowDto -> spreadsheetRowDto.getUserName()));

        Map<String, String> sendIdMap = Maps.newHashMap();
        if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.WECHAT) {
            List<OneClickStaff> oneClickStaffList = new ArrayList<>();
            rowList.forEach(spreadsheetRowDto -> {
                OneClickStaff oneClickStaff = new OneClickStaff();
                oneClickStaff.setSpreadSheetRowId(spreadsheetRowDto.getId());
                oneClickStaff.setMobileNo(spreadsheetRowDto.getMobileNo());
                oneClickStaffList.add(oneClickStaff);
            });
            sendIdMap = irenshiDubboProviderService.getAppUserRecordServiceId(oneClickStaffList);
            // TODO 产品确认不做异常提醒，直接发送，没有openId的不发送。
            /*if (sendIdMap.size() == 0) {
                throw new OneClickException("one_click_term_get_wechat_open_id_error", "获取微信openId失败，微信服务异常!");
            }*/
        } else if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.SMS) {
            sendIdMap = rowList.stream().collect(Collectors.toMap(SpreadsheetRowDto::getId, SpreadsheetRowDto::getMobileNo));
        } else if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.PROGRAM) {
            List<OneClickStaff> oneClickStaffList = new ArrayList<>();
            rowList.forEach(spreadsheetRowDto -> {
                OneClickStaff oneClickStaff = new OneClickStaff();
                oneClickStaff.setSpreadSheetRowId(spreadsheetRowDto.getId());
                oneClickStaff.setMobileNo(spreadsheetRowDto.getMobileNo());
                oneClickStaffList.add(oneClickStaff);
            });
            sendIdMap = irenshiDubboProviderService.getAppUserRecordProgramId(oneClickStaffList);
        } else if (messageSendTask.getMessageType() == MessageSendRequest.MessageType.EMAIL) {
            for (SpreadsheetRowDto spreadsheetRowDto : rowList) {
                if (StringUtils.isNotBlank(spreadsheetRowDto.getEmail())) {
                    sendIdMap.put(spreadsheetRowDto.getId(),spreadsheetRowDto.getEmail());
                }
            }
        } else {
            throw new OneClickException("one_click_term_send_type_param_error", "发送类型参数错误!");
        }

        // 获取消息模板
        MessageTemplate messageTemplate = messageTemplateService.getMessageTemplate(MessageTemplate.Service.ONECLICKCLOUD,
                SpreadsheetRowDto.class.getName(), messageSendRequest.getMessageType(), messageSendRequest.getLocal());
        if (messageTemplate == null) {
            throw new OneClickException("one_click_term_no_service_send_message_template", "没有获取到服务的发送模板，请联系管理员。");
        }

        String sendContentTemplate = messageTemplate.getTemplate();
        String vendorTemplateId = messageTemplate.getVendorTemplateId();

        for (SpreadsheetRowDto spreadsheetRowDto : rowList) {
            String rowId = spreadsheetRowDto.getId();
            if (!sendIdMap.containsKey(rowId)) {
                logger.error("publish spreadsheet row id sendId is null. staffRecordId: {}", rowId);
                continue;
            }
            String sendId = sendIdMap.get(rowId);
            if (StringUtils.isBlank(sendId)) {
                logger.error("sendId is null, staffRecordId: {}", rowId);
                continue;
            }
            if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.SMS) {
                String shorUrl = irenshiWeixinService.getShorUrl(url+rowId);
                messageList.add(MessageSendRecord.generateOneClickSms(sendId, sendContentTemplate, vendorTemplateId, companyName, shorUrl, month, rowId));
            } else if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.WECHAT) {
                messageList.add(MessageSendRecord.generateOneClickWechatMessage(sendId, sendContentTemplate, vendorTemplateId, companyName, messageDesc, month, rowId));
            } else if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.PROGRAM) {
                messageList.add(MessageSendRecord.generateOneClickProgramMessage(sendId, sendContentTemplate, vendorTemplateId, companyName, messageDesc, month, rowId));
            } else if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.EMAIL) {
                String shorUrl = irenshiWeixinService.getShorUrl(url+rowId);
                File file = QrCodeUtils.createQrCode(shorUrl);
                InputStream inputStream = null;
                try {
                    byte[] bytes = new byte[0];
                    inputStream = new FileInputStream(file);
                    bytes = new byte[inputStream.available()];
                    inputStream.read(bytes);
                    inputStream.close();
                    String imgQRcodeBase64 = Base64.getEncoder().encodeToString(bytes);
                    messageList.add(MessageSendRecord.generateOneClickEmailMessage(sendId, sendContentTemplate, vendorTemplateId, companyName, spreadsheetRowDto.getUserName(), month, rowId, imgQRcodeBase64,url));
                }catch (FileNotFoundException e) {
                    logger.error("文件转换失败",e);
                } catch (IOException e) {
                    logger.error("文件错误",e);
                }
            } else {
                logger.error("Not support the send type: {}", messageSendRequest.getMessageType());
            }
        }
        messageSendTask.setMessageList(messageList);
        if (messageSendRequest.getMessageType() == MessageSendRequest.MessageType.SMS) {
            // 计算总短信条数，每条短信长度限制在65个字以内
            int totalSmsCount = 0;
            for (MessageSendRecord messageSendRecord : messageList) {
                double smsContentLength = messageSendRecord.getContent().length();
                totalSmsCount = totalSmsCount + (smsContentLength <= 65 ? 1 : (int) Math.ceil(smsContentLength / 65d));
            }

            //如果是发送短信 处理免费发送100条的逻辑
            CompanyFreeMessageDto companyFreeMessageDto = companyFreeMessageDao.findByCompanyId(companyId);
            int freeSmsCount = companyFreeMessageDto == null ? smsConfig.getFreeSmsCount() : companyFreeMessageDto.getSmsFreeCount();
            if (freeSmsCount < totalSmsCount) {
                // 如果免费额度不足则计算公司账户额度是否充足
                int paySmsCount = totalSmsCount - freeSmsCount;
                if (!irenshiDubboProviderService.balanceSufficient(companyId, paySmsCount)) {
                    throw new OneClickException("one_click_term_company_account_balance_insufficient", "发送失败，账户余额不足，请去充值公司账户！");
                }
            }

        }
        return messageSendTask;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveSpreadsheet(SpreadsheetVo spreadsheet) {
        SpreadsheetDto spreadsheetDto = new SpreadsheetDto();
        BeanConvertUtils.convertFor(spreadsheetDto, spreadsheet);
        if (spreadsheetDto.getId() != null) {
            spreadsheetDto = spreadsheetDao.findOne(spreadsheet.getId());
            SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findOne(spreadsheet.getId());
            // 通过MD5验证表头是否修改过
            String columnJsonArraySerialize = JSONArray.toJSONString(spreadsheet.getColumnList());
            String md5sum = MD5Utils.MD5(columnJsonArraySerialize);
            if (!md5sum.equals(spreadSheetRowHeaderDto.getMd5Sum())) {
                throw new OneClickException("one_click_spreadsheet_row_header_differ", "表头不一致，请检查后重新导入！");
            }
            spreadsheetDto.setUpdatedAt(new Date());
            spreadsheetDao.save(spreadsheetDto);
        } else {
            // TODO 保存表头信息
            //保存固定表头信息
            List<SpreadsheetColumnDto> fixedColumnList = new ArrayList<>();
            spreadsheet.getColumnList().forEach(column -> {
                if (column.getFixedColumn()) {
                    fixedColumnList.add(BeanConvertUtils.convertFor(column, new SpreadsheetColumnDto()));
                }
            });
            spreadsheetDto.setColumns(fixedColumnList);
            spreadsheetDto.setCreatedAt(new Date());
        }
        List<List<Object>> rowValueList = spreadsheet.getRowValueList();
        List<SpreadsheetRowDto> spreadsheetRowDtoList = new ArrayList<>();
        rowValueList.forEach(valueList -> {
            SpreadsheetRowDto spreadsheetRowDto = new SpreadsheetRowDto();
            // TODO 待完善，通过反射动态添加固定字段值
            //spreadsheetRowDto.setDataStr(JSONArray.toJSONString(valueList));
            spreadsheetRowDto.setData(valueList);
            spreadsheetRowDtoList.add(spreadsheetRowDto);
        });
        spreadsheetDto.getRows().addAll(spreadsheetRowDtoList);
        spreadsheetDao.save(spreadsheetDto);
    }

    @Override
    public void deleteSpreadsheet(String spreadsheetId) {
        spreadsheetDao.delete(spreadsheetId);
    }

    @Override
    public void deleteSpreadsheetBatch(List<String> spreadsheetIdList) {
        // 删除固定列
        spreadsheetColumnDao.deleteBySpreadsheetIdIn(spreadsheetIdList);

        spreadsheetTempRowDao.deleteBySpreadsheetIdIn(spreadsheetIdList);

        // 删除电子表格
        List<SpreadsheetDto> spreadsheetDtoList = spreadsheetDao.findByIdIn(spreadsheetIdList);
        spreadsheetDao.delete(spreadsheetDtoList);

        // 删除表头
        spreadsheetRowHeaderDao.deleteBySpreadsheetIdIn(spreadsheetIdList);

    }

    @Override
    public List<RowHeaderColumn> getRowHeader(String spreadsheetId) {
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetId);
        if (spreadSheetRowHeaderDto == null) {
            return Lists.newArrayList();
        }
        //List<RowHeaderColumn> list = JSONArray.parseArray(spreadSheetRowHeaderDto.getDataStr(), RowHeaderColumn.class);
        List<RowHeaderColumn> list = spreadSheetRowHeaderDto.getData();
        return list.stream().filter(rowHeaderColumn -> rowHeaderColumn.getFixedColumn() == null || !rowHeaderColumn.getFixedColumn()).collect(Collectors.toList());
    }

    @Override
    public List<RowHeaderColumn> getFootHeader(String spreadsheetId) {
        SpreadsheetFootColumnDto spreadsheetFootColumnDto = spreadsheetFootColumnDao.findBySpreadsheetId(spreadsheetId);
        if (spreadsheetFootColumnDto == null || spreadsheetFootColumnDto.getData() == null) {
            return Lists.newArrayList();
        }
        return JSONArray.parseArray(spreadsheetFootColumnDto.getDataStr(), RowHeaderColumn.class);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveFootColumn(SpreadsheetFootColumnRequest request) {
        String spreadsheetId = request.getSpreadsheetId();
        SpreadsheetFootColumnDto spreadsheetFootColumnDto = spreadsheetFootColumnDao.findBySpreadsheetId(spreadsheetId);
        if (spreadsheetFootColumnDto == null) {
            spreadsheetFootColumnDto = new SpreadsheetFootColumnDto();
            spreadsheetFootColumnDto.setSpreadsheetId(spreadsheetId);
        }
        List<FootColumnVo> footColumnVoList = request.getFootColumnList();
        if (CollectionUtils.isEmpty(footColumnVoList)) {
            return;
        }
        List<RowHeaderColumn> footColumnList = new ArrayList<>();
        List<RowHeaderColumn> rowHeaderList = rowHeaderColumnService.getSpreadsheetRowHeaderColumnList(spreadsheetId);
        Map<String, RowHeaderColumn> headerColumnMap = rowHeaderList.stream().collect(Collectors.toMap(RowHeaderColumn::getColumnDisplayName, column -> column));
        footColumnList.addAll(footColumnVoList.stream().
                filter(footColumnVo -> footColumnVo != null && footColumnVo.getChoose() && headerColumnMap.containsKey(footColumnVo.getColumnDisplayName())).
                map(footColumnVo -> headerColumnMap.get(footColumnVo.getColumnDisplayName())).collect(Collectors.toList()));
        spreadsheetFootColumnDto.setDataStr(JSON.toJSONString(footColumnList));
        spreadsheetFootColumnDao.save(spreadsheetFootColumnDto);
        //保存合计数据
        this.saveFootData(spreadsheetId);
    }

    /**
     * 保存合计数据
     */
    @Override
    public void saveFootData(String spreadsheetId) {
        //更新spreadSheet中合计数据
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        SpreadsheetFootColumnDto footColumnDto = spreadsheetFootColumnDao.findBySpreadsheetId(spreadsheetId);
        if (footColumnDto == null || footColumnDto.getData() == null) {
            return;
        }
        List<RowHeaderColumn> footColumnList = JSONArray.parseArray(footColumnDto.getDataStr(), RowHeaderColumn.class);
        if (CollectionUtils.isEmpty(footColumnList)) {
            spreadsheetDto.setFooter(null);
            spreadsheetDao.save(spreadsheetDto);
            return;
        }
        if (spreadsheetDto == null) {
            return;
        }
        List<SpreadsheetRowDto> rowDtoList = spreadsheetRowService.getAllRow(spreadsheetId);
        Map<String, Object> stringDoubleMap = new HashMap<>();
        List<List<Object>> rowValueList = new ArrayList<>();
        rowDtoList.forEach(rowDto ->
                //rowValueList.add(JSONArray.parseArray(rowDto.getDataStr(), Object.class))
                rowValueList.add(rowDto.getData())
        );
        footColumnList.forEach(column ->
                stringDoubleMap.put(column.getColumnDisplayName(), itemValueTotal(rowValueList, column.getColumnIndex()))
        );
        spreadsheetDto.setFootDataStr(JSON.toJSONString(stringDoubleMap));
        spreadsheetDao.save(spreadsheetDto);
    }

    @Override
    public void withdrawRowPublish(List<String> spreadSheetRowId, String spreadsheetId) {
        List<SpreadsheetRowDto> spreadsheetRowDtoList = null;
        if (!CollectionUtils.isEmpty(spreadSheetRowId)) {
            spreadsheetRowDtoList = spreadsheetRowDao.findByIdIn(spreadSheetRowId);
        } else {
            spreadsheetRowDtoList = spreadsheetRowDao.findBySpreadsheetId(spreadsheetId);
        }

        spreadsheetRowDtoList.forEach(spreadsheetRowDto -> {
            spreadsheetRowDto.setPublishStatus(false);
        });
        spreadsheetRowDao.save(spreadsheetRowDtoList);
    }

    @Override
    @Transactional
    public void oneclickPublishSpreadSheet(String companyId, SpreadsheetPublishRequest spreadsheetPublishRequest) {
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetPublishRequest.getSpreadsheetId());
        if (spreadsheetDto == null) {
            throw new OneClickException("one_click_spreadsheet_data_error", "数据异常！");
        }
        spreadsheetDto.setAppendedName(spreadsheetPublishRequest.getAppendedName());
        spreadsheetDao.save(spreadsheetDto);
        spreadsheetPublishRequest.setSpreadSheetRowIds(null);
        spreadsheetPublishRequest.setNotice(true);
        publishSpreadSheetRow(companyId, spreadsheetPublishRequest);
    }

    private String itemValueTotal(List<List<Object>> rowValueList, int cellIndex) {
        BigDecimal total = new BigDecimal(0);
        for (List<Object> cellValueList : rowValueList) {
            try {
                Object value = cellValueList.get(cellIndex);
                String valueStr = value == null ? null : value.toString();
                BigDecimal valueBigDecimal = StringUtils.isBlank(valueStr) ? new BigDecimal(0) :
                        new BigDecimal(valueStr);
                total = total.add(valueBigDecimal);
            } catch (Exception e) {
                //该列转换异常时，代表该列不可统计
                return NOT_AVALIABLE;
            }
        }
        return total.doubleValue() + "";
    }
}
